package com.hnichr.crm.common.report.repository;

import com.mxpioframework.security.entity.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author Wp
 * @date 2022/12/24 0024
 */
@Repository
public interface RptRepository extends JpaRepository<User,Long> {

    @Query(value = "select \n" +
            "a.customer_id as customerId,\n" +
            "a.customer_name as customerName,\n" +
            "sum(receipt_money) amount \n" +
            "from crm_receipt a \n" +
            "left join crm_customer c on a.customer_id = c.id\n" +
            "group by a.customer_id,a.customer_name\n" +
            "order by amount desc\n" ,
            countQuery = "select count(*)" +
                    "from crm_receipt a \n" +
                    "left join crm_customer c on a.customer_id = c.id\n" +
                    "group by a.customer_id,a.customer_name\n" +
                    "order by amount desc\n"
            , nativeQuery = true)
    Page<Map<String,Object>> findTop10Customer(Pageable pageable);

    @Query(value = "SELECT\n" +
            " customer_id AS customerId,\n" +
            " customer_name AS customerName,\n" +
            " SUM(\n" +
            " IFNULL( money, 0 )) AS money \n" +
            "FROM\n" +
            " crm_contract \n" +
            "GROUP BY\n" +
            " customer_id,\n" +
            " customer_name \n" +
            "ORDER BY\n" +
            " money DESC" ,
            countQuery = "SELECT\n" +
                    " COUNT(*) \n" +
                    "FROM\n" +
                    " crm_contract \n" +
                    "GROUP BY\n" +
                    " customer_id,\n" +
                    " customer_name \n"
            , nativeQuery = true)
    Page<Map<String,Object>> findTop10(Pageable pageable);

    @Query(value = "select " +
            "date_format(create_time, '%Y-%m-%d') create_time, " +
            "count(id) num " +
            "from " +
            "crm_product " +
            " where create_time between ? and ?"+
            "group by " +
            "date_format(create_time, '%Y-%m-%d') " +
            "order by create_time", nativeQuery = true)
    public List<Map<String,Object>> getIncreaseProject(Date begin, Date end);
}
